﻿Imports MICROFORMA.BEL
Imports MICROFORMA.DIL
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.Common

Public Class SolicitudDA
    Implements SolicitudDI


    Public Function Add(ByVal _solicitud_BE As BEL.SolicitudBE) As String Implements DIL.SolicitudDI.Add
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Try
            '_solicitud_BE.Fecha _
            Dim query As String = "Insert into Solicitud (IdTipo, Fecha, IdCliente, IdUsuario, DetalleSolicitud, FechaModificacion, Estado)" _
                                    & "Values (" & _solicitud_BE.IdTipo & ",'20121201'" _
                                    & "," & _solicitud_BE.IdCliente & "," & _solicitud_BE.IdUsuario & ",'" & _solicitud_BE.DetalleSolicitud & "',getdate(),0)"


            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)


            db.ExecuteNonQuery(o_dbCommand)


            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function

    Public Function Delete(ByVal Id As Integer) As String Implements DIL.SolicitudDI.Delete
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Try
            Dim query As String = "Update Solicitud Set estado=1" _
                                 & ", FechaModificacion=" & "getDate()" _
                                 & " where IdSolicitud=" & Id


            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)


            db.ExecuteNonQuery(o_dbCommand)


            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function

    Public Function Edit(ByVal _solicitud_BE As BEL.SolicitudBE) As String Implements DIL.SolicitudDI.Edit
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Try
            Dim query As String = "Update Solicitud Set IdCliente=" & _solicitud_BE.IdCliente _
                                & ",DetalleSolicitud='" & _solicitud_BE.DetalleSolicitud & "'" _
                                 & ",FechaModificacion=" & "getDate()" _
                                 & " where IdSolicitud=" & _solicitud_BE.IdSolicitud


            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)


            db.ExecuteNonQuery(o_dbCommand)


            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function

    Public Function getIdLastRecord() As Integer Implements DIL.SolicitudDI.getIdLastRecord
        Dim idSolicitud As Integer = 0

        Dim db As Database = DatabaseFactory.CreateDatabase

        Dim query As String = "select max(IdCliente) from Clientes"

        Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim o_Cnx As DbConnection = db.CreateConnection()

        o_Cnx.Open()

        Dim o_Transaction As DbTransaction = o_Cnx.BeginTransaction

        Try
            idSolicitud = Convert.ToInt32(db.ExecuteScalar(o_dbCommand)) + 1


        Catch ex As Exception
            o_Transaction.Rollback()
            Return idSolicitud
        Finally
            o_Cnx.Close()
        End Try

        Return idSolicitud


    End Function

    Public Function GetList() As System.Data.DataTable Implements DIL.SolicitudDI.GetList

        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim query As String = "select s.*, (select nombre from clientes c where c.idcliente = s.idcliente) cliente from Solicitud s where estado = 0"

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim productsDataSet As DataSet = Nothing

        productsDataSet = db.ExecuteDataSet(dbCommand)

        Return productsDataSet.Tables(0)

    End Function

    Public Function List() As DataTable Implements DIL.SolicitudDI.List
        Dim dr As DbDataReader = Nothing

        Dim o_BECollec_Solicitud_BE As New SolicitudBECollection

        Dim o_Solicitud_BE As SolicitudBE

        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim query As String = "select s.*, (select nombre from clientes c where c.idcliente = s.idcliente) cliente from Solicitud s where estado=0"

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim solicitudDataSet As DataSet = Nothing

        solicitudDataSet = db.ExecuteDataSet(dbCommand)

        Return solicitudDataSet.Tables(0)

        'While dr.Read()
        '    o_Solicitud_BE = New SolicitudBE
        '    o_Solicitud_BE.IdSolicitud = dr("IdSolicitud")
        '    o_Solicitud_BE.IdTipo = dr("IdTipo")
        '    o_Solicitud_BE.Fecha = dr("Fecha")
        '    o_Solicitud_BE.IdCliente = dr("IdCliente")
        '    o_Solicitud_BE.IdUsuario = dr("IdUsuario")
        '    o_Solicitud_BE.DetalleSolicitud = dr("DetalleSolicitud")
        '    o_Solicitud_BE.FechaModificacion = dr("FechaModificacion")
        '    o_Solicitud_BE.Estado = dr("Estado")

        '    If dr("Estado") = "0" Then
        '        o_Solicitud_BE.EstadoDescripcion = "Activo"
        '    ElseIf dr("Estado") = "1" Then
        '        o_Solicitud_BE.EstadoDescripcion = "Bloqueado"
        '    End If

        '    o_BECollec_Solicitud_BE.Add(o_Solicitud_BE)
        '    o_Solicitud_BE = Nothing
        'End While

        'Return o_BECollec_Solicitud_BE

    End Function


    Public Function List_Id(ByVal Id As Integer) As DataTable Implements DIL.SolicitudDI.List_Id
        Dim dr As DbDataReader = Nothing

        Dim o_BECollec_Solicitud_BE As New SolicitudBECollection

        Dim o_Solicitud_BE As SolicitudBE

        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim query As String = "select s.*, (select nombre from clientes c where c.idcliente = s.idcliente) cliente from Solicitud s where s.idsolicitud = " & Id

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim solicitudDataSet As DataSet = Nothing

        solicitudDataSet = db.ExecuteDataSet(dbCommand)

        Return solicitudDataSet.Tables(0)

        'While dr.Read()
        '    o_Solicitud_BE = New SolicitudBE
        '    o_Solicitud_BE.IdSolicitud = dr("IdSolicitud")
        '    o_Solicitud_BE.IdTipo = dr("IdTipo")
        '    o_Solicitud_BE.Fecha = dr("Fecha")
        '    o_Solicitud_BE.IdCliente = dr("IdCliente")
        '    o_Solicitud_BE.IdUsuario = dr("IdUsuario")
        '    o_Solicitud_BE.DetalleSolicitud = dr("DetalleSolicitud")
        '    o_Solicitud_BE.FechaModificacion = dr("FechaModificacion")
        '    o_Solicitud_BE.Estado = dr("Estado")

        '    If dr("Estado") = "0" Then
        '        o_Solicitud_BE.EstadoDescripcion = "Activo"
        '    ElseIf dr("Estado") = "1" Then
        '        o_Solicitud_BE.EstadoDescripcion = "Bloqueado"
        '    End If

        '    o_BECollec_Solicitud_BE.Add(o_Solicitud_BE)
        '    o_Solicitud_BE = Nothing
        'End While

        'Return o_BECollec_Solicitud_BE

    End Function
End Class
